﻿using System;
using System.Collections.Generic;
using System.Configuration;
using Citi.PTSConsolidation.Domain;
using Citi.PTSConsolidation.Domain.Utils;
using Citi.PTSConsolidation.SchedulerUI.Bases;
using Citi.RoutineBase;

namespace Citi.PTSConsolidation.SchedulerUI
{
    public class EntrepotExtraction
    {
        private static string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }
        private static string EntrepotConnectionString { get { return ConfigurationManager.AppSettings["EntrepotConnectionString"]; } }
        private static string ExcelConnectionString { get { return ConfigurationManager.AppSettings["ExcelConnectionString"]; } }

        public static void ExtractData(ICurrentAction currentAction, IDictionary<string, object> parameters, string tableName, int year, string robotUsername, string robotPassword, DateTime referenceDate)
        {
            currentAction.Description = "Starting Extraction";

            var ptsTableName = "BookOfWork";

            ArchiveOldData(currentAction, year, ptsTableName);
            DropPreviousData(currentAction, year, ptsTableName);

            currentAction.StartLog("Extracting");
            ExtractDataFromEntrepot(year, tableName, currentAction, robotUsername, robotPassword);
            currentAction.StopLog();

            DeleteNonBr(currentAction, year, ptsTableName);
            ValidateFullYear(currentAction, year, ptsTableName);
            //UpdateProjectAccount1Sum(currentAction, year);
            //UpdateProjectAccount1SystemGocSum(currentAction, year);
            //UpdateProjectClientGocSum(currentAction, year);
            //UpdateSystemVarianceTable(currentAction, year, referenceDate);

            currentAction.Description = "Extraction Finished";
        }

        //private static void UpdateSystemVarianceTable(ICurrentAction currentAction, int year, DateTime referenceDate)
        //{
        //    currentAction.Description = "Updating System Variance Table";
        //    var query = BookOfWork.GetSaveSystemVarianceQuery(year, referenceDate);
        //    currentAction.Description = query;
        //    SqlHelper.ExecuteReader(query, PtsConnectionString);
        //}

        //private static void UpdateProjectClientGocSum(ICurrentAction currentAction, int year)
        //{
        //    currentAction.Description = "Updating Project and Client GOC Sum";
        //    var query = BookOfWork.GetSumProjectClientGOCUpdateQuery(year, DateTime.Today);
        //    currentAction.Description = query;
        //    SqlHelper.ExecuteReader(query, PtsConnectionString);
        //}

        //private static void UpdateProjectAccount1SystemGocSum(ICurrentAction currentAction, int year)
        //{
        //    currentAction.Description = "Updating Project, Account 1 and System GOC Sum";
        //    var query = BookOfWork.GetSumProjectAccount1SystemGOCUpdateQuery(year, DateTime.Today);
        //    currentAction.Description = query;
        //    SqlHelper.ExecuteReader(query, PtsConnectionString);
        //}

        //private static void UpdateProjectAccount1Sum(ICurrentAction currentAction, int year)
        //{
        //    currentAction.Description = "Updating Project and Account 1 Sum";
        //    var query = BookOfWork.GetSumProjectAccount1UpdateQuery(year, DateTime.Today);
        //    currentAction.Description = query;
        //    SqlHelper.ExecuteReader(query, PtsConnectionString);
        //}

        private static void ValidateFullYear(ICurrentAction currentAction, int year, string ptsTableName)
        {
            try
            {
                //if (year <= DateTime.Today.Year)
                {
                    currentAction.Description = "Validating FullYear";
                    var query = GenericBookOfWork.GetQueryValidateFullYear(ptsTableName, year, DateTime.Today);
                    currentAction.Description = query;
                    var fullYear = SqlHelper.ExecuteReader(query, PtsConnectionString);

                    if (fullYear.Rows[0][0].ToString() == "1")
                        currentAction.Description = "FullYear validated";
                    else
                        throw new InvalidOperationException("FullYear Sum is invalid. This routine must run again");
                }
            }
            catch (InvalidOperationException e)
            {
                throw e;
            }
            catch
            {
                currentAction.Description = "Error on validating";
            }
        }

        private static void DeleteNonBr(ICurrentAction currentAction, int year, string ptsTableName)
        {
            currentAction.Description = "Deleting Data Non-BR";
            var query = GenericBookOfWork.GetQueryDeleteNonBr(ptsTableName, year);
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        private static void DropPreviousData(ICurrentAction currentAction, int year, string ptsTableName)
        {
            currentAction.Description = "Dropping previous data";
            var query = GenericBookOfWork.GetQueryDuplicateData(ptsTableName, year, DateTime.Today);
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        private static void ArchiveOldData(ICurrentAction currentAction, int year, string ptsTableName)
        {
            var daysToArchive = 7;
            currentAction.Description = string.Format("Archiving old data. Last {0} days will be archived.", daysToArchive);
            var query = GenericBookOfWork.GetQueryHistoryBOW(ptsTableName, year, daysToArchive);
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        private static void ExtractDataFromEntrepot(int year, string tableName, ICurrentAction currentAction, string robotUsername, string robotPassword)
        {
            currentAction.Description = string.Format("Table PTS Entrepot to Extract: {0}", tableName);

            currentAction.Description = string.Format("Entrepot Year {0}: Copy has started", year);
            ExtractData(year, tableName, currentAction); 
            currentAction.Description = string.Format("Entrepot Year {0}: Copy has finished", year);
        }

        private static void ExtractData(int year, string tableName, ICurrentAction action)
        {
            var query = BookOfWork.GetEntrepotQueryString(tableName);

            action.Description = query;

            SqlHelper.CopyDataFromAnotherDatabase(EntrepotConnectionString, query, string.Concat("BookOfWork", year),
                    PtsConnectionString, BookOfWork.BookOfWorkColumnMappings);
        }
    }

    public class EntrepotExtractionCurrentYear : CurrentYear
    {
        static string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }

        protected override void ExecuteProcess(DateTime referenceDate, ref ICurrentAction currentAction, string networkUser, string networkPassword, Dictionary<string, object> parameters)
        {
            if (DateTime.Today.Month == 12 || DateTime.Today.Month == 1)
            {
                var year = DateTime.Today.Month == 12 ? DateTime.Today.Year + 2 : DateTime.Today.Year + 1;

                currentAction.Description = string.Format("Checking if BookOfWork{0} exists. If it doesn't exist it will be created.", year);
                var queryCreateTableNewYear = BookOfWork.GetQueryCreateTableNewYear(year, false);
                SqlHelper.ExecuteReader(queryCreateTableNewYear, PtsConnectionString);

                //currentAction.Description = string.Format("Creating BookOfWorkHistory{0} table.", year);
                //queryCreateTableNewYear = BookOfWork.GetQueryCreateTableNewYear(year, true);
                //SqlHelper.ExecuteReader(queryCreateTableNewYear, PtsConnectionString);
            }

            const string tableName = EntrepotDatabaseNames.BookOfWork.TableNameCurrentYear;
            EntrepotExtraction.ExtractData(currentAction, parameters, tableName, DateTime.Today.Year, networkUser, networkPassword, referenceDate);
        }
    }

    public class EntrepotExtractionNextYear : NextYear
    {
        protected override void ExecuteProcess(DateTime referenceDate, ref ICurrentAction currentAction, string networkUser, string networkPassword, Dictionary<string, object> parameters)
        {
            EntrepotExtraction.ExtractData(currentAction, parameters, EntrepotDatabaseNames.BookOfWork.TableNameNextYear, referenceDate.Year + 1, networkUser, networkPassword, referenceDate);
        }
    }

    public class EntrepotExtractionPreviousYear : PreviousYear
    {
        protected override void ExecuteProcess(DateTime referenceDate, ref ICurrentAction currentAction, string networkUser, string networkPassword, Dictionary<string, object> parameters)
        {
            EntrepotExtraction.ExtractData(currentAction, parameters, EntrepotDatabaseNames.BookOfWork.TableNamePreviousYear, DateTime.Today.Year - 1, networkUser, networkPassword, referenceDate);
        }
    }
}
